﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Configuration;

#pragma warning disable 618

namespace Core
{
	class SqlServerAccountStorage : IAccountStorage
	{
		string m_ConnectionString = "";

		public SqlServerAccountStorage()
		{
			Configuration config = WebConfigurationManager.OpenWebConfiguration(
				HttpContext.Current.Request.ApplicationPath == "/" ? "/Lesktop" : HttpContext.Current.Request.ApplicationPath + "/Lesktop"
			);

			m_ConnectionString = config.ConnectionStrings.ConnectionStrings["Lesktop_ConnectString"].ConnectionString;
		}

		DataRow IAccountStorage.GetAccountInfo(string name)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "GetAccountInfoByName";

			cmd.Parameters.Add("name", DbType.String).Value = name;

			SqlDataAdapter ada = new SqlDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows.Count > 0 ? dt.Rows[0] : null;
		}

		DataRow IAccountStorage.GetAccountInfo(Int64 key)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = "GetAccountInfoByID";
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("id", DbType.Int64).Value = key;

			SqlDataAdapter ada = new SqlDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows.Count > 0 ? dt.Rows[0] : null;
		}

		DataRowCollection IAccountStorage.GetAllUsers()
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"GetAllUsers";
			cmd.CommandType = CommandType.StoredProcedure;

			SqlDataAdapter ada = new SqlDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows;
		}

		DataRowCollection IAccountStorage.GetAllGroups()
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"GetAllGroups";
			cmd.CommandType = CommandType.StoredProcedure;

			SqlDataAdapter ada = new SqlDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows;
		}

		DataRowCollection IAccountStorage.GetFriends(string name)
		{
			DataTable result = new DataTable();

			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = "GetFriends";
			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add("name", DbType.String).Value = name;

			SqlDataAdapter ada = new SqlDataAdapter();
			ada.SelectCommand = cmd;

			ada.Fill(result);
			ada.Dispose();

			return result.Rows;
		}

		Int64 IAccountStorage.GetRelationship(string account1, string account2)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"GetRelationship";
			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add("account1", DbType.String).Value = account1;
			cmd.Parameters.Add("account2", DbType.String).Value = account2;

			DataTable result = new DataTable();

			SqlDataAdapter ada = new SqlDataAdapter();

			ada.SelectCommand = cmd;
			ada.Fill(result);
			ada.Dispose();

			return result.Rows.Count > 0 ? Convert.ToInt64(result.Rows[0]["Relationship"]) : -1;
		}

		bool IAccountStorage.Validate(string name, string password)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = "Validate";
			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add("name", DbType.String).Value = name;
			cmd.Parameters.Add("password", DbType.String).Value = Utility.MD5(password);

			SqlDataAdapter ada = new SqlDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			return dt.Rows.Count > 0;
		
		}

		String[] IAccountStorage.GetUserRoles(string name)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = "GetUserRoles";
			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add("name", DbType.String).Value = name;

			SqlDataAdapter ada = new SqlDataAdapter();
			ada.SelectCommand = cmd;

			DataTable dt = new DataTable();
			ada.Fill(dt);
			ada.Dispose();

			List<string> names = new List<string>();
			foreach (DataRow row in dt.Rows) names.Add(row["RoleName"] as string);

			return names.ToArray();
		}

		void IAccountStorage.AddFriend(string user, string friend)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"AddFriend";
			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add("user", DbType.String).Value = user;
			cmd.Parameters.Add("friend", DbType.String).Value = friend;

			conn.Open();
			try
			{
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.DeleteFriend(Int64 userId, Int64 friendId)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText = @"DeleteFriend";
			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add("user", DbType.Int64).Value = userId;
			cmd.Parameters.Add("friend", DbType.Int64).Value = friendId;

			conn.Open();
			try
			{
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.DeleteUser(Int64 id)
		{

			SqlConnection conn = new SqlConnection(m_ConnectionString);
			conn.Open();
			try
			{
				SqlCommand cmd = new SqlCommand(@"DeleteUser");
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("id", DbType.Int64).Value = id;
				cmd.Connection = conn;
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.DeleteGroup(Int64 id)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			conn.Open();
			try
			{
				SqlCommand cmd = new SqlCommand(@"DeleteGroup");
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("id", DbType.Int64).Value = id;
				cmd.Connection = conn;
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.CreateUser(String name, String nickname, String password, String email)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			conn.Open();
			try
			{
				SqlCommand insertUser = new SqlCommand("CreateUser", conn);
				insertUser.CommandType = CommandType.StoredProcedure;

				insertUser.Parameters.Add("name", DbType.String).Value = name;
				insertUser.Parameters.Add("password", DbType.String).Value = Utility.MD5(password);
				insertUser.Parameters.Add("nickname", DbType.String).Value = nickname;
				insertUser.Parameters.Add("email", DbType.String).Value = email;
				insertUser.Parameters.Add("inviteCode", DbType.String).Value = Guid.NewGuid().ToString().ToUpper().Replace("-", "");

				insertUser.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
        }

        void IAccountStorage.CreateTempUser(String name, String nickname)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            conn.Open();
            try
            {
                SqlCommand insertUser = new SqlCommand("CreateTempUser", conn);
                insertUser.CommandType = CommandType.StoredProcedure;

                insertUser.Parameters.Add("name", DbType.String).Value = name;
                insertUser.Parameters.Add("nickname", DbType.String).Value = nickname;

                insertUser.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

		void IAccountStorage.CreateGroup(String creator, String name, String nickname)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);
			conn.Open();
			try
			{
				SqlCommand cmd = new SqlCommand("CreateGroup", conn);
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("creator", DbType.String).Value = creator;
				cmd.Parameters.Add("name", DbType.String).Value = name;
				cmd.Parameters.Add("nickname", DbType.String).Value = nickname;
				cmd.Parameters.Add("inviteCode", DbType.String).Value = Guid.NewGuid().ToString().ToUpper().Replace("-", "");

				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		void IAccountStorage.UpdateUserInfo(string name, Hashtable values)
		{
			SqlConnection conn = new SqlConnection(m_ConnectionString);

			conn.Open();
			try
			{
				if (values.ContainsKey("Password"))
				{
					if (!values.ContainsKey("PreviousPassword")) throw new Exception("原密码错误！");

					SqlCommand checkPwdCmd = new SqlCommand();
					checkPwdCmd.Connection = conn;
					checkPwdCmd.CommandText = "select [Key] from Users where UpperName = @Name and Password = @Password";

					checkPwdCmd.Parameters.Add("Name", DbType.String).Value = name.ToUpper();
					checkPwdCmd.Parameters.Add("Password", DbType.String).Value = Utility.MD5(values["PreviousPassword"].ToString());

					object val = checkPwdCmd.ExecuteScalar();

					if (val == null) throw new Exception("原密码错误！");
				}

				StringBuilder cmdText = new StringBuilder();
				cmdText.Append("update Users set Name = Name");
				if (values.ContainsKey("Nickname")) cmdText.Append(",Nickname = @Nickname");
				if (values.ContainsKey("Password")) cmdText.Append(",Password = @Password");
				if (values.ContainsKey("EMail")) cmdText.Append(",EMail = @EMail");
				if (values.ContainsKey("InviteCode")) cmdText.Append(",InviteCode = @InviteCode");
				if (values.ContainsKey("AcceptStrangerIM")) cmdText.Append(",AcceptStrangerIM = @AcceptStrangerIM");
				if (values.ContainsKey("MsgFileLimit")) cmdText.Append(",MsgFileLimit = @MsgFileLimit");
				if (values.ContainsKey("MsgImageLimit")) cmdText.Append(",MsgImageLimit = @MsgImageLimit");
				if (values.ContainsKey("HomePage")) cmdText.Append(",HomePage = @HomePage");
				if (values.ContainsKey("HeadIMG")) cmdText.Append(",HeadIMG = @HeadIMG");
				if (values.ContainsKey("Remark")) cmdText.Append(",Remark = @Remark");
				cmdText.Append(" where UpperName=@UpperName");
				if (values.ContainsKey("PreviousPassword")) cmdText.Append(" and Password = @PreviousPassword");
				SqlCommand cmd = new SqlCommand();
				cmd.Connection = conn;
				cmd.CommandText = cmdText.ToString();

				if (values.ContainsKey("Nickname")) cmd.Parameters.Add("Nickname", DbType.String).Value = values["Nickname"];
				if (values.ContainsKey("Password")) cmd.Parameters.Add("Password", DbType.String).Value = Utility.MD5(values["Password"] as string);
				if (values.ContainsKey("EMail")) cmd.Parameters.Add("EMail", DbType.String).Value = values["EMail"];
				if (values.ContainsKey("InviteCode")) cmd.Parameters.Add("InviteCode", DbType.String).Value = values["InviteCode"];

				if (values.ContainsKey("AcceptStrangerIM")) cmd.Parameters.Add("AcceptStrangerIM", DbType.Int64).Value = ((bool)values["AcceptStrangerIM"]) ? 1 : 0;
				if (values.ContainsKey("MsgFileLimit")) cmd.Parameters.Add("MsgFileLimit", DbType.Int64).Value = Convert.ToInt64((Double)values["MsgFileLimit"]);
				if (values.ContainsKey("MsgImageLimit")) cmd.Parameters.Add("MsgImageLimit", DbType.Int64).Value = Convert.ToInt64((Double)values["MsgImageLimit"]);

				if (values.ContainsKey("HomePage")) cmd.Parameters.Add("HomePage", DbType.String).Value = values["HomePage"];
				if (values.ContainsKey("HeadIMG")) cmd.Parameters.Add("HeadIMG", DbType.String).Value = values["HeadIMG"];
				if (values.ContainsKey("Remark")) cmd.Parameters.Add("Remark", DbType.String).Value = values["Remark"];

				cmd.Parameters.Add("UpperName", DbType.String).Value = name.ToUpper();
				if (values.ContainsKey("PreviousPassword")) cmd.Parameters.Add("PreviousPassword", DbType.String).Value = Utility.MD5(values["PreviousPassword"] as string);

				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		String[] IAccountStorage.GetGroupManagers(string name)
		{
			DataTable result = new DataTable();

			SqlConnection conn = new SqlConnection(m_ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			cmd.CommandText =@"GetGroupManagers";
			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add("name", DbType.String).Value = name;

			SqlDataAdapter ada = new SqlDataAdapter();
			ada.SelectCommand = cmd;

			ada.Fill(result);
			ada.Dispose();

			List<String> names = new List<string>();
			foreach (DataRow row in result.Rows) names.Add(row["Name"] as string);

			return names.ToArray();
		}

	}
}
#pragma warning restore 618